package com.example.spring.multi.basic.tools.excel;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.excel.util.StringUtils;
import com.example.spring.multi.module.logic.controller.other.model.SignInDateModel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @ClassName ExcelParseUtil
 * @description: excel解析工具类
 * @author: tomato
 * @create: 2023-07-18 14:43
 * @Version 1.0
 **/
@Slf4j
public class ExcelParseUtil {

    // 使用Map保存所有人员信息，统计打开时长
    private static Map<String, SignInDateModel> signInDateModelsMap = new HashMap<>();

    private static String pathname = "/Users/fanxiaodiu/Desktop/File/打卡/";

    //使用MultipartFile 流进行的Excel的解析
    public static List<Object> parseUploadUgcCoupon(MultipartFile file){
        ExcelReader reader;
        try {
            reader = ExcelUtil.getReader(file.getInputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        // 读取表头信息,并且将表头信息转换成对应的实体类
        reader.setIgnoreEmptyRow(true);
        reader.addHeaderAlias("视频id", "groupId").read(2);
        reader.addHeaderAlias("活动号", "actyId").read(2);
        reader.addHeaderAlias("礼品号", "giftId").read(2);
        reader.addHeaderAlias("科目号", "subjectId").read(2);
        reader.addHeaderAlias("有效期描述", "lifespan").read(2);
        reader.addHeaderAlias("优惠券类型", "type").read(2);
        reader.addHeaderAlias("金额", "category").read(2);
        reader.addHeaderAlias("视频来源", "source").read(2);
        reader.addHeaderAlias("开始时间", "startTime").read(2);
        reader.addHeaderAlias("结束时间", "endTime").read(2);
        reader.addHeaderAlias("描述", "description").read(2);
        List<Object> list =  reader.read(1,2, Object.class);
        if (Objects.isNull(list) || list.size() == 0){
            return null;
        }
        return list;
    }

    // 生成优惠券Excel模板
    public static XSSFWorkbook getXssfWorkbook() {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("Sheet1");
        // 创建标题行并设置样式
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("数据模板");

        XSSFCellStyle titleCellStyle = xssfWorkbook.createCellStyle();
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
        titleCell.setCellStyle(titleCellStyle);

        // 在第二行填充数据
        XSSFRow dataRow = sheet.createRow(1);
        //设置第二行字体格式
        XSSFCellStyle dataCellStyle = xssfWorkbook.createCellStyle();
        XSSFFont dataFont = xssfWorkbook.createFont();
        dataFont.setFontName("宋体");
        dataFont.setFontHeightInPoints((short) 11);
        dataCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataCellStyle.setFont(dataFont);

        // 假设有一个字符串数组存储数据
        String[] data = {"视频id", "活动号", "礼品号", "科目号", "有效期描述", "金额","优惠券类型", "视频来源", "开始时间", "结束时间", "描述"};
        for (int i = 0; i < data.length; i++) {
            XSSFCell dataCell = dataRow.createCell(i);
            dataCell.setCellValue(data[i]);
            dataCell.setCellStyle(dataCellStyle);
            sheet.setColumnWidth(i, (data[i].length() + 10) * 256); // 设置列宽为数据长度加上10的偏移量
        }
        return xssfWorkbook;
    }

    //创建新的Excel工作簿，填充数据，返回
    public static void responseExcelData(HttpServletResponse response, List<Object> favorResponses) {
        // 生成Excel文件
        // 创建ExcelWriter对象
        ExcelWriter writer = ExcelUtil.getWriter();
        // 设置表头
        writer.addHeaderAlias("articleId", "文章ID");
        writer.addHeaderAlias("author", "作者ID");
        writer.addHeaderAlias("favorNum", "点赞数");
        writer.write(favorResponses);
        // 根据内容自动设置列宽
        writer.autoSizeColumnAll();
        // 设置列宽为自动适应列宽的基础上再加10
        writer.setColumnWidth(-1, 10);
        String fileName = "点赞数据统计.xlsx";
        // 获取输出流
        OutputStream outputStream = null;
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8));
            outputStream = response.getOutputStream();
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 刷新ExcelWriter中的数据到输出流
        writer.flush(outputStream, true);
        // 关闭writer，释放资源
        writer.close();
        // 关闭输出流
        IoUtil.close(outputStream);
    }

    public static void buildSignInDateModels(String id) {
        File folder = new File(pathname + id);
        processFilesFromFolder(folder);
        if (signInDateModelsMap.size() > 0) {
            List<SignInDateModel> signInDateModels = new ArrayList<>(signInDateModelsMap.values());
            // 生成Excel文件
            // 创建ExcelWriter对象
            ExcelWriter writer = ExcelUtil.getWriter();
            // 设置表头
            writer.addHeaderAlias("name", "姓名");
            writer.addHeaderAlias("sex", "性别");
            writer.addHeaderAlias("type", "类型");
            writer.addHeaderAlias("idCard", "身份证号");
            writer.addHeaderAlias("signInTime", "签到时间");
            writer.addHeaderAlias("signOutTime", "签退时间");
            writer.addHeaderAlias("workTime", "打卡时长(h)");

            writer.write(signInDateModels);
            // 根据内容自动设置列宽
            writer.autoSizeColumnAll();
            // 设置列宽为自动适应列宽的基础上再加10
            writer.setColumnWidth(-1, 10);
            String fileName = "打卡数据统计.xlsx";
            // 获取输出流
            OutputStream outputStream = null;
            try {
                outputStream = new FileOutputStream(pathname + id + "/打卡数据统计.xlsx");
                // 刷新ExcelWriter中的数据到输出流
                writer.flush(outputStream, true);
                // 关闭writer，释放资源
                writer.close();
                // 关闭输出流
                IoUtil.close(outputStream);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    // 递归遍历文件夹下的所有文件和子文件夹
    private static void processFilesFromFolder(File folder) {
        if (folder.isDirectory()) {
            File[] files = folder.listFiles();
            if (files != null) {
                for (File file : files) {
                    if (file.isDirectory()) {
                        processFilesFromFolder(file);
                    } else {
                        processExcelFile(file);
                    }
                }
            }
        } else {
            processExcelFile(folder);
        }
    }

    private static void processExcelFile(File file) {
        log.info("开始处理文件：{}", file.getName());
        //如果文件不是excel文件，直接返回
        if(file.getName().indexOf("xls") == -1 && file.getName().indexOf("xlsx") == -1) {
            log.info("{}不是excel文件", file.getName());
            return;
        }
        ExcelReader reader = ExcelUtil.getReader(file);
        // 读取表头信息,并且将表头信息转换成对应的实体类
        reader.addHeaderAlias("姓名", "name");
        reader.addHeaderAlias("性别", "sex");
        reader.addHeaderAlias("类型", "type");
        reader.addHeaderAlias("身份证号", "idCard");
        reader.addHeaderAlias("签到时间", "signInTime");
        reader.addHeaderAlias("签退时间", "signOutTime");
        reader.addHeaderAlias("打卡时长(h)", "workTime");
        List<SignInDateModel> dataList =  reader.readAll(SignInDateModel.class);

        // 关闭reader，释放资源
        reader.close();

        // 将dataList转换为实体对象，并进行数据处理操作

//        List<SignInDateModel> processedData = processData(dataList);
        // 将处理后的数据进行统计
        statisticsData(dataList);

        // 将处理后的数据写入原来的Excel文件
//        writeDataToExcel(file, processedData);

    }

    private static void writeDataToExcel(File file, List<SignInDateModel> processedData) {
        ExcelWriter writer = ExcelUtil.getWriter();
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("sex", "性别");
        writer.addHeaderAlias("type", "类型");
        writer.addHeaderAlias("idCard", "身份证号");
        writer.addHeaderAlias("signInTime", "签到时间");
        writer.addHeaderAlias("signOutTime", "签退时间");
        writer.addHeaderAlias("workTime", "打卡时长(h)");

        writer.write(processedData);
        // 根据内容自动设置列宽
        writer.autoSizeColumnAll();
        // 设置列宽为自动适应列宽的基础上再加10
        writer.setColumnWidth(-1, 10);

        writer.flush(file);
        writer.close();
    }

    private static void statisticsData(List<SignInDateModel> processedData) {

        if (Objects.isNull(processedData) || processedData.size() == 0) {
            return;
        }
        // 将数据按照姓名进行分组
        Map<String, List<SignInDateModel>> collect = processedData.stream().collect(Collectors.groupingBy(SignInDateModel::getIdCard));
        // 遍历分组后的数据，将每个人的数据按照日期进行分组
        collect.forEach((cardId, list) -> {
           //如果map中存在cardId，则将list中用户的打开时长累加然后和map中value的时长相加，保存至map中，没有则累加，将cardId作为key，用户信息为value，时长为累加的值
            if (signInDateModelsMap.containsKey(cardId)) {
                // 获取map中的value
                SignInDateModel signInDateModel = signInDateModelsMap.get(cardId);
                // 获取map中的时长,去掉单位h
                Double workTime = signInDateModel.getWorkTime();
                // 将list中的时长进行累加
                Double collect1 = list.stream().mapToDouble(SignInDateModel::getWorkTime).sum();
                // 将累加后的时长和map中的时长进行累加
                signInDateModel.setWorkTime(workTime + collect1);
                // 将累加后的值设置到map中
                signInDateModelsMap.put(cardId, signInDateModel);
            } else {
                // 如果map中不存在cardId，则将list中用户的打开时长累加，将cardId作为key，用户信息为value，时长为累加的值
                Double collect1 = list.stream().mapToDouble(SignInDateModel::getWorkTime).sum();
                SignInDateModel statisticsModel = new SignInDateModel();
                statisticsModel.setWorkTime(collect1);
                statisticsModel.setName(list.get(0).getName());
                statisticsModel.setSex(list.get(0).getSex());
                statisticsModel.setType(list.get(0).getType());
                statisticsModel.setIdCard(list.get(0).getIdCard());
                statisticsModel.setSignInTime(list.get(0).getSignInTime());
                statisticsModel.setSignOutTime(list.get(0).getSignOutTime());
                signInDateModelsMap.put(cardId, statisticsModel);
            }
        });
    }

    // 对实体对象进行数据处理操作
    private static List<SignInDateModel> processData(List<SignInDateModel> entityList) {
        List<SignInDateModel> processedData = new ArrayList<>();
        if (Objects.isNull(entityList) || entityList.size() == 0) {
            return processedData;
        }
        // 进行数据处理操作，将集合中的对象遍历，将签到时间和签退时间进行处理，如果签退时间和签到时间都不为空，则计算出工作时长，如果签退时间为空，则工作时长为0
        entityList.forEach(entity -> {
            String signInTime = entity.getSignInTime();
            String signOutTime = entity.getSignOutTime();
            if (StringUtils.isNotBlank(signInTime) && StringUtils.isNotBlank(signOutTime)) {
                // 计算工作时长,将工作时长转换成小时设置到实体对象中
                entity.setWorkTime(getIntersectionTime(signInTime, signOutTime));
            } else {
                entity.setWorkTime((double) 0);
            }
            processedData.add(entity);
        });
        return processedData;
    }

    public static double getIntersectionTime(String signIn1, String signIn2) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");

        try {
            Date startDate1 = format.parse(signIn1);
            Date endDate1 = format.parse(signIn2);

//            Date range1StartTime = getTimeWithHoursAndMinutes(startDate1,8, 0);
//            Date range1EndTime = getTimeWithHoursAndMinutes(startDate1, 9, 30);
//            Date range2StartTime = getTimeWithHoursAndMinutes(startDate1, 9, 50);
//            Date range2EndTime = getTimeWithHoursAndMinutes(startDate1, 11, 20);
//            Date range3StartTime = getTimeWithHoursAndMinutes(startDate1, 13, 0);
//            Date range3EndTime = getTimeWithHoursAndMinutes(startDate1, 14, 30);
//            Date range4StartTime = getTimeWithHoursAndMinutes(startDate1, 14, 50);
//            Date range4EndTime = getTimeWithHoursAndMinutes(startDate1, 16, 20);

            Date range1StartTime = getTimeWithHoursAndMinutes(startDate1,7, 0);
            Date range1EndTime = getTimeWithHoursAndMinutes(startDate1, 8, 30);
            Date range2StartTime = getTimeWithHoursAndMinutes(startDate1, 8, 50);
            Date range2EndTime = getTimeWithHoursAndMinutes(startDate1, 10, 20);
            Date range3StartTime = getTimeWithHoursAndMinutes(startDate1, 14, 0);
            Date range3EndTime = getTimeWithHoursAndMinutes(startDate1, 15, 30);
            Date range4StartTime = getTimeWithHoursAndMinutes(startDate1, 15, 50);
            Date range4EndTime = getTimeWithHoursAndMinutes(startDate1, 17, 20);


            double intersection1 = calculateIntersection(startDate1, endDate1, range1StartTime, range1EndTime);
            double intersection2 = calculateIntersection(startDate1, endDate1, range2StartTime, range2EndTime);
            double intersection3 = calculateIntersection(startDate1, endDate1, range3StartTime, range3EndTime);
            double intersection4 = calculateIntersection(startDate1, endDate1, range4StartTime, range4EndTime);

            //保留小数点后两位
            return (double) Math.round((intersection1 + intersection2 + intersection3 + intersection4)*100)/100;
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return 0;
    }
    private static Date getTimeWithHoursAndMinutes(Date startDate1, int hours, int minutes) {
        Date currentDate = new Date();
        //设置currentDate的年月日为startDate1的年月日
        currentDate.setYear(startDate1.getYear());
        currentDate.setMonth(startDate1.getMonth());
        currentDate.setDate(startDate1.getDate());
        currentDate.setHours(hours);
        currentDate.setMinutes(minutes);
        currentDate.setSeconds(0);
        return currentDate;
    }


    public static double calculateIntersection(Date startDate1, Date endDate1, Date startDate2, Date endDate2) {
        // Calculate the intersection by finding the maximum of the start dates
        // and the minimum of the end dates
        long start = Math.max(startDate1.getTime(), startDate2.getTime());
        long end = Math.min(endDate1.getTime(), endDate2.getTime());
        double intersection = (double) (end - start) / (1000 * 60 * 60); // Convert milliseconds to hours
        return Math.max(intersection, 0); // Return 0 if there is no intersection
    }

    public static void buildSignInDateModels1() {
        File folder = new File(pathname);
        processFilesFromFolder(folder);
    }
}
